package gob.osinerg.emergencia.dao;

import gob.osinerg.emergencia.bean.AccidenteGravePreliminar;
import gob.osinerg.emergencia.bean.ClienteSIGED;
import gob.osinerg.emergencia.bean.FormularioEmergenciaUnmay;
import gob.osinerg.emergencia.util.Report;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.util.ResourceUtils;

public class AccidenteGravePreliminarDAOImpl extends GenericDAOImpl implements AccidenteGravePreliminarDAO{

	public void save(AccidenteGravePreliminar bean, FormularioEmergenciaUnmay beanform) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		try {			
			transaction = session.beginTransaction();
			if(beanform.getId() == null){
				beanform.setId(getNextPK("FRM_EMR_UM_SEQ"));
			}
			
			if(bean.getId() == null){
				bean.setId(getNextPK("ACCDNTE_GRVE_PRLMNAR_SEQ"));
			}
			
			if(beanform.getNmroEmrgncia() == null){
				beanform.setNmroEmrgncia(bean.getIdFormato());
			}
			session.saveOrUpdate(beanform);
			
			bean.setIdFormularioEmergencia(beanform.getId());
			session.saveOrUpdate(bean);
			transaction.commit();
			
		} catch (Exception e) {
			transaction.rollback();
			throw e;
		}finally{
			session.close();
		}
	}
	
	// 1 = OK | 0 = error
	public int save(AccidenteGravePreliminar bean, FormularioEmergenciaUnmay beanform, String logo) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		int resultado = 0;
		try {			
			transaction = session.beginTransaction();
			if(beanform.getId() == null){
				beanform.setId(getNextPK("FRM_EMR_UM_SEQ"));
			}
			
			if(bean.getId() == null){
				bean.setId(getNextPK("ACCDNTE_GRVE_PRLMNAR_SEQ"));
			}
			
			if(beanform.getNmroEmrgncia() == null){
				beanform.setNmroEmrgncia(bean.getIdFormato());
			}
			//AGREGANDO UN ESTADO PARA MOSTRARLO EN EL MENU PRINCIPAL 
			beanform.setFlagTemporal("NO");
			
			session.saveOrUpdate(beanform);
			
			bean.setIdFormularioEmergencia(beanform.getId());
			session.saveOrUpdate(bean);
			
			transaction.commit();
			
			System.out.println("id: "+bean.getId());
			
			
			resultado = 1;
			
			// SIGED
			/*
			boolean commit = false;
			enviarExpediente(beanform.getId(), logo);
			if(commit) {
				resultado = 1;
				transaction.commit();
			}else {
				resultado = 0;
				transaction.rollback();
			}
			*/
			
		} catch (Exception e) {
			resultado = 0;
			transaction.rollback();
			throw e;
		}finally{
			session.close();
		}
		
		return resultado;
	}

    public Long getNextPK(String sequence) throws Exception {
        long result = 1;

        StringBuffer sql = new StringBuffer("");
        sql.append("SELECT ");
        sql.append(sequence);
        sql.append(".NEXTVAL AS ID FROM DUAL");        
        
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = getSession().connection().prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            if(rs.next()) {
                result = rs.getLong(1);
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) {
            log.error(e.getMessage());
            if(pstmt != null) {
                try {pstmt.close();} catch(Exception ignore) {}
            }
            if(rs != null) {
            	try {rs.close();}catch(Exception ignore) {}
            }
        }
        return new Long(result);
    }	

    /*
    private void enviarExpediente(Long id, String logo){
    	try {
	    	Map parameters = new HashMap();
	        parameters.put("ID", id);
	        parameters.put("IMAGE_LOGO", logo);
	        //parameters.put("IMAGE_LOGO", request.getRequestURL().substring(0, request.getRequestURL().lastIndexOf("/"))+"/images/logo_home.jpg");
	
	        File archivo = ResourceUtils.getFile("classpath:gob/osinerg/emergencia/resource/jrxml/AccidenteGravePreliminar.jrxml");
	        
	        byte[] bytes = null; 
			Report report = new Report();
			report.setJasperReport(archivo);
			report.setParameter(parameters);
			report.setConnection(getSession().connection());
			bytes = report.getBytesPDF();
			if (bytes != null && bytes.length > 0) {
				
			}else{
				System.out.println("ERROR no llegaron bytes");
			}
    	} catch(Exception e) {
    		System.out.println("ERROR enviarExpediente: "+ e.getMessage());
    	}
    }
    */

	@Override
	public int saveExpedienteSiged(FormularioEmergenciaUnmay beanform,
			String numeroExpedienteSiged) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		int resultado = 0;
		try{
			transaction = session.beginTransaction();
			
			beanform.setNumeroExpediente(numeroExpedienteSiged);
			session.saveOrUpdate(beanform);
			transaction.commit();
			resultado = 1;
			
		}catch(Exception e) {
			resultado = 0;
			transaction.rollback();
		}finally{
			session.close();
		}
		
		return resultado;
	}

	@Override
	public void eliminarAccidenteGravePreliminar(AccidenteGravePreliminar bean) throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.delete(bean);
			transaction.commit();
		} catch(Exception e) {
			transaction.rollback();
		} finally{
			session.close();
		}
		
	}

	@Override
	public void eliminarEmergenciaUnMay(FormularioEmergenciaUnmay beanform)
			throws Exception {
		Session session = getSession();
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.delete(beanform);
			transaction.commit();
		} catch(Exception e) {
			transaction.rollback();
		} finally{
			session.close();
		}
		
	}

	@Override
	public ClienteSIGED datosClienteSigedPorRuc(String ruc) throws Exception {
		PreparedStatement pstmt = null;
        ResultSet rs = null;
        ClienteSIGED cliente= null;
       
        try {
               String sql =" select c.idcliente AS IDCLIENTE, "+
                           " c.tipoidentificacion AS TIPOIDENTIFICACION, " +
               		    " c.nroidentificacion AS NROIDENTIFICACION, " +
               		    " c.nombres AS NOMBRE, " +
               		    " c.apellidopaterno AS APELLIDOPATERNO, " +
               		    " c.apellidomaterno AS APELLIDOMATERNO, " +
               		    " c.razonsocial AS RAZONSOCIAL, "+
               	        " c.direccionprincipal AS DIRECCIONPRINCIPAL, " +
               	        " c.direccionalternativa AS DIRECCIONALTERNATIVO, " +
               	        " c.telefono AS TELEFONO, " +
               	        " c.ubigeoprincipal AS UBIGEOPRINCIPAL, " +
               	        " c.estado AS ESTADO "+
               	        " from cliente c "+
               	        " where c.nroidentificacion =? ";
               	        
            System.out.println("sql ==> getClienteByRuc return Obj ClienteSIGED "+sql);             
            pstmt = getSession().connection().prepareStatement(sql);
            pstmt.setString(1, ruc);
            rs = pstmt.executeQuery();
            while (rs.next()) {
            	cliente= new ClienteSIGED();
            	cliente.setIdcliente(Integer.valueOf(String.valueOf(rs.getInt("IDCLIENTE"))));
            	cliente.setCodigoTipoIdentificacion(Integer.valueOf(String.valueOf(rs.getInt("TIPOIDENTIFICACION"))));
            	cliente.setNroIdentificacion(rs.getString("NROIDENTIFICACION"));
            	cliente.setNombre(rs.getString("NOMBRE"));
            	cliente.setApellidoPaterno(rs.getString("APELLIDOPATERNO"));
            	cliente.setApellidoMaterno(rs.getString("APELLIDOMATERNO"));
            	cliente.setRazonSocial(rs.getString("RAZONSOCIAL"));
            	cliente.setDireccionPrincipal(rs.getString("DIRECCIONPRINCIPAL"));
            	cliente.setDireccionAlternativo(rs.getString("DIRECCIONALTERNATIVO"));
            	cliente.setTelefono(rs.getString("TELEFONO"));
            	cliente.setUbigeoPrincipal(Integer.valueOf(String.valueOf(rs.getInt("UBIGEOPRINCIPAL"))));
            	cliente.setEstado(rs.getString("ESTADO"));
            
            }
            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;
        }
        catch(Exception e) { 
            log.error(e.getMessage());
            if(pstmt != null) {
                try {
                    pstmt.close();
                }
                catch(Exception ignore) {
                }
            }
            if(rs != null) {
                try {
                    rs.close();
                }
                catch(Exception ignore) {
                }
            }
        }
        return cliente;
	}
	
	public String getNumeroMesaParte(Long uniopeId, Long unifisId) throws Exception {
		String nroMesaPartes="0";
		Connection con = null;
		CallableStatement cstmt = null;
        String sql = "{ call SFHDESA02.EMRGNCIAS_MSA_PRTES_STD(?,?,?,?,?)}";
        
		try{
			cstmt = getSession().connection().prepareCall(sql);
			cstmt.setString(1, "Informe de Emergencias");
			cstmt.setInt(2, Integer.parseInt(String.valueOf(uniopeId)));
			cstmt.setString(3, "B2");
			cstmt.setInt(4, Integer.parseInt(String.valueOf(unifisId)));
			cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
			cstmt.execute();
			nroMesaPartes = String.valueOf(cstmt.getInt(5));
            cstmt.close();
            cstmt = null;
            
		}catch (Exception e) {
        	log.error(e);
        }finally{
        	 if (cstmt != null) {
                 try {
                	 cstmt.close();
                 }
                 catch (SQLException ignore) {
                 }
                 cstmt = null;
             }	
        }
		return nroMesaPartes;

	}
	
	public String correlativoFuncionSecuencia(String numeroTipoDocumento,String folio,String sistema,String instanciaBD) throws Exception {
        String numeroSecuencia=null;
        CallableStatement cstmt = null;
        try {
            String sql = "{? = call SGD_FUN_OBTENER_NUMERACION(?,?,?,?)}";
            cstmt = getSession().connection().prepareCall(sql);
            cstmt.setString(2,numeroTipoDocumento);
            cstmt.setString(3,folio);
            cstmt.setString(4,sistema);
            cstmt.setString(5,instanciaBD);
            cstmt.registerOutParameter(1,Types.VARCHAR);             
            cstmt.execute();
            numeroSecuencia = cstmt.getString(1);
            if(numeroSecuencia==null){
            	numeroSecuencia = "";
            }
            //numeroSecuencia= StringUtils.nullAsEmptyString(cstmt.getString(1));                                      
            cstmt.close();
            cstmt = null;
        } catch(Exception e) {
            log.error(e.getMessage(),e);
            if(cstmt != null) {
                try {
                    cstmt.close();
                } catch(Exception ignore) {
                }
            }
        }
        return numeroSecuencia;
    }
	
	public String getCorrelativo(long idCorrelativo)throws Exception {
        String correlativo="";
        CallableStatement statement = null;
        
        try {
            String sql = "{? = call SGD_FUN_OBTENER_NUMERACION(?,'100','SFHDESA02', 'SFH')}";
            statement = getSession().connection().prepareCall(sql);
            statement.setLong(2,idCorrelativo);
            statement.registerOutParameter(1,java.sql.Types.VARCHAR);
            statement.execute();
            correlativo = String.valueOf(statement.getString(1));
            statement.close();
            statement = null;
        } catch(Exception e) {
            log.error(e.getMessage());
            if(statement != null) {
                try {
               	 statement.close();
                } catch(Exception ignore) {
                }
            }
            throw new Exception(e);
        }
        return correlativo;
    }
    
}
